clear
set more off

cap log close
log using "$logdir/get_data/nrp/get_nrp.log", replace


set odbcmgr REDACTED

clear

odbc list
#delimit ;

odbc load, exec("

				select
					convert(bigint,a.REDACTED) as person_id,
					coalesce(a.REDACTED,0) as wgs_pre,
					coalesce(a.REDACTED,0) as wgs_post,
					coalesce(a.REDACTED,0) as interest_pre,
					coalesce(a.REDACTED,0) as interest_post,
					coalesce(a.REDACTED,0) as dividends_pre,
					coalesce(a.REDACTED,0) as dividends_post,
					coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) + 
						coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) + 
						coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) as sched1_pre,
					coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) + 
						coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) + 
						coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) as sched1_post,
					coalesce(a.REDACTED,0) + 
						coalesce(a.REDACTED,0) as ira_pens_pre,
					coalesce(a.REDACTED,0) + 
						coalesce(a.REDACTED,0) as ira_pens_post,
					coalesce(a.REDACTED,0) as capgain_pre,
					coalesce(a.REDACTED,0) as capgain_post,
					coalesce(a.REDACTED,0) as agi_pre,
					coalesce(a.REDACTED,0) as agi_post,
					coalesce(a.REDACTED,0) as ti_pre,
					coalesce(a.REDACTED,0) as ti_post,
					coalesce(a.REDACTED,0) as eitc_pre,
					coalesce(a.REDACTED,0) as eitc_post,
					coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) as ctc_pre,
					coalesce(a.REDACTED,0) +
						coalesce(a.REDACTED,0) as ctc_post,
					b.REDACTED as nrp_wgt
				from REDACTED as a
					inner join REDACTED as b
						on(a.REDACTED=b.REDACTED and a.REDACTED = b.REDACTED)
				where a.REDACTED = '201412'
") 
	dsn("REDACTED") user($usr) password($pwd) sqlshow;


#delimit cr
di _N
cap destring person_id, replace force
cap destring sched1* ctc* ira_pens*, replace force

rename person_id tin
merge 1:1 tin using $tinsdir/masking_old2new, keep(3) nogen
drop tin
rename new_tin prim_tin
cap destring prim_tin, replace

qui save $nrpdir/nrp_raw, replace

cap log close

